package fr.mywiki.model.queries;

import java.rmi.RemoteException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import org.jdom.Document;
import org.jdom.JDOMException;

import fr.mywiki.business.entry.EntryLite;
import fr.mywiki.business.entry.meta.PropertyFactory;
import fr.mywiki.business.entry.meta.EntryModel;
import fr.mywiki.business.entry.meta.ModelException;
import fr.mywiki.business.entry.meta.Property;
import fr.mywiki.business.xml.XmlManager;
import fr.mywiki.model.ejbAdapter.DataController;
import fr.mywiki.model.ejbAdapter.utils.PersistenceException;
import fr.mywiki.model.ejbAdapter.utils.Sequencer;

/** Methods for the management of models objects in the database. */
public class DatabaseEntryModelController {
	/* Models management */
	
	/**
	 * Creates a new empty model in the database.
	 * 
	 * @return the created <code>EntryModel</code> object.
	 */
	public static EntryModel createModel() throws PersistenceException, SQLException {
		// Generate a new model identifier
		long id = getNewModelId();

		// Create the model
		String sql = "INSERT INTO "+EntryModel.TYPE_ENT+" VALUES ("+id+", NULL);";
		
		DatabaseAdapter.executeUpdateQuery(sql);
		
		EntryModel model = new EntryModel();
		model.setId(new Long(id));
		
		return model;
	}
	
	/**
	 * Removes an entry model.
	 * 
	 * @param name
	 *          the name of the model
	 * @throws ModelException
	 *           if the model having that name does not exist.
	 */
	public static void removeModel(EntryModel entry) throws ModelException, SQLException {
		// Remove the linked properties
		removeProperties(entry);
		
		// Remove the model
		String sql = "DELETE FROM " + EntryModel.TYPE_ENT + " WHERE id="
				+ entry.getId().toString() + ";";
		DatabaseAdapter.executeUpdateQuery(sql);
	}
	
	/**
	 * Removes the properties linked to the specified model.
	 * 
	 * @param entry
	 *          a <code>EntryModel</code> object.
	 */
	private static void removeProperties(EntryModel entry) throws SQLException {
		String sql = "DELETE FROM " + Property.TYPE_ENT + " WHERE "
				+ Property.LINK_ENTRY_MODEL + "=" + entry.getId().toString() + ";";
		DatabaseAdapter.executeUpdateQuery(sql);
	}
	
	/**
	 * Modifies the model in the database.
	 * @param model a <code>EntryModel</code> object.
	 */
	public static void updateModel(EntryModel model) throws ModelException,
			SQLException, PersistenceException {
		// Update the model itself -> name
		String sql = "UPDATE " + EntryModel.TYPE_ENT + " SET "
				+ EntryModel.FIELD_NAME + "='" + model.getName() + "' WHERE id="
				+ model.getId().toString() + ";";
		
		DatabaseAdapter.executeUpdateQuery(sql);
		
		// Remove the properties and re-add them.
		removeProperties(model);
		Iterator itProps = model.iterateProperties();
		while (itProps.hasNext()) {
			Property prop = (Property) itProps.next();
			linkPropertyInDatabase(prop, model);
		}
	}
	
	/**
	 * Creates the property in the database and links it to the model.
	 * 
	 * @param prop
	 *          a <code>Property</code> object to be added.
	 * @param model
	 *          a <code>EntryModel</code> object (already created).
	 * @throws SQLException 
	 * @throws PersistenceException 
	 */
	private static void linkPropertyInDatabase(Property prop, EntryModel model) throws PersistenceException, SQLException {
		String xmlDesc = XmlManager.XmlToString(prop.toXmlDoc());
		
		String sql = "INSERT INTO " + Property.TYPE_ENT + " VALUES ("
				+ getNewModelPropertyId() + ", " + model.getId().toString() + ", '"
				+ prop.getName() + "', '" + xmlDesc + "');";
		
		DatabaseAdapter.executeUpdateQuery(sql);
	}
	
	/**
	 * Loads all the entry models from the database.
	 * 
	 * @return a <code>Collection</code> of <code>EntryModel</code> objects
	 *         with their associated properties.
	 * @throws SQLException 
	 * @throws ModelException 
	 * @throws JDOMException 
	 */
	public static Collection findAllModels() throws SQLException, ModelException, JDOMException {
		ArrayList models = new ArrayList();
		
		String sql = "SELECT id FROM " + EntryModel.TYPE_ENT + ";";
		Iterator itResult = DatabaseAdapter.executeSelectQuery(sql).iterator();
		
		while(itResult.hasNext()) {
			Long modelId = (Long)((Object[])itResult.next())[0];
			models.add(getModel(modelId));
		}
		
		return models;
	}
	
	/**
	 * Returns true if a model with the specified identifier exists.
	 * 
	 * @param id the identifier of the model.
	 * @return true if a model having this id exists, false otherwise.
	 * @throws SQLException 
	 */
	public static boolean existsModel(long id) throws SQLException{
		String sql = "SELECT * FROM " + EntryModel.TYPE_ENT + " WHERE id="
				+ id + ";";
		ResultList res = DatabaseAdapter.executeSelectQuery(sql);
		return !res.isEmpty();
	}
	
	/**
	 * Returns true if a model property with the specified identifier exists.
	 * 
	 * @param id the identifier of the model property.
	 * @return true if a model property having this id exists, false otherwise.
	 * @throws SQLException 
	 */
	private static boolean existsModelProperty(long id) throws SQLException{
		String sql = "SELECT * FROM " + Property.TYPE_ENT + " WHERE id="
				+ id + ";";
		ResultList res = DatabaseAdapter.executeSelectQuery(sql);
		return !res.isEmpty();
	}
	
	/**
	 * Returns true if a model with the specified name exists.
	 * 
	 * @param name the name of the model.
	 * @return true if a model having this id exists, false otherwise.
	 * @throws SQLException 
	 */
	public static boolean existsModel(String name) throws SQLException{
		String sql = "SELECT * FROM " + EntryModel.TYPE_ENT + " WHERE "
				+ EntryModel.FIELD_NAME + "='" + name + "';";
		ResultList res = DatabaseAdapter.executeSelectQuery(sql);
		return !res.isEmpty();
	}
	
	/**
	 * Loads a complete entry model from the database.
	 * 
	 * @param id the identifier of the model.
	 * @return a <code>EntryModel</code> object and associated properties.
	 * @throws ModelException if the database model is disfunctional.
	 */
	public static EntryModel getModel(Long id) throws SQLException, ModelException, JDOMException {
		String sql = "SELECT id, " + EntryModel.FIELD_NAME + " FROM "
				+ EntryModel.TYPE_ENT + " WHERE id=" + id.toString() + ";";
		ResultList res = DatabaseAdapter.executeSelectQuery(sql);
		if(res.isEmpty())
			// The model does not exist.
			return null;
		
		EntryModel model = new EntryModel();
		model.setId(id);
		
		String modelName =(String) res.getRecord(0)[1]; 
		model.setName(modelName);
		
		// Get the properties linked to this model
		sql = "SELECT id," + Property.FIELD_NAME + ", "
				+ Property.FIELD_DESCRIPTION + " FROM " + Property.TYPE_ENT
				+ " WHERE " + Property.LINK_ENTRY_MODEL + "=" + id;
		
		Iterator itProps = DatabaseAdapter.executeSelectQuery(sql).iterator();
		while(itProps.hasNext()) {
			String desc = (String) ((Object[]) itProps.next())[2];
			Document descXml = XmlManager.stringToXml(desc);
			model.addProperty(PropertyFactory.createProperty(descXml));
		}

		// String modelDescription =(String) res.getRecord(0)[2];
		// Document descXml = XmlManager.stringToXml(modelDescription);
		// EntryModelManager.fillModelDescriptionFromXmlDocument(model,descXml);
		
		return model;
	}
	
	/**
	 * Loads all the entry models whose name is the specified one.
	 * 
	 * @param name
	 *          the name of the models
	 * @return a <code>Collection</code> of <code>EntryModel</code> objects
	 *         with associated properties.
	 * @throws JDOMException 
	 */
	public static Collection getModels(String name) throws SQLException, ModelException, JDOMException {
		ArrayList models = new ArrayList();
		String sql = "SELECT id FROM " + EntryModel.TYPE_ENT + " WHERE name='"
				+ name + "'";
		
		Iterator itResult = DatabaseAdapter.executeSelectQuery(sql).iterator();
		while (itResult.hasNext()) {
			Long modelId = (Long) ((Object[]) itResult.next())[0];
			models.add(getModel(modelId));
		}
		
		return models;
	}
	
	/**
	 * Returns a new model identifier. Not already used.
	 * 
	 * @return a long integer.
	 * @throws PersistenceException 
	 * @throws SQLException 
	 */
	private static long getNewModelId() throws PersistenceException, SQLException {
		long id = Sequencer.getInstance(EntryModel.TYPE_ENT).next();
		while(existsModel(id)) {
			id = Sequencer.getInstance(EntryModel.TYPE_ENT).next();
		}
		return id;
	}
	
	/**
	 * Returns a new model property identifier.
	 * 
	 * @return a long integer.
	 * @throws PersistenceException 
	 * @throws SQLException 
	 */
	private static long getNewModelPropertyId() throws PersistenceException, SQLException {
		long id = Sequencer.getInstance(Property.TYPE_ENT).next();
		while(existsModelProperty(id)) {
			id = Sequencer.getInstance(Property.TYPE_ENT).next();
		}
		return id;
	}
	
	/* Entry-model links management */
	/** The name of the table containing the entry-model links. */
	private static final String ENTRY_MODEL_TABLE = "entry_model";
	/** The name of the column where the entries identifiers are stored. */
	private static final String ENTRY_MODEL_ENTRY = "id_entry";
	/** The name of the column where the models identifiers are stored. */
	private static final String ENTRY_MODEL_MODEL = "id_model";
	
	/**
	 * Returns the models linked to the entry given in parameter.
	 * 
	 * @param entry a <code>EntryLite</code> object.
	 * @return a Collection of <code>EntryModel</code> objects.
	 */
	public static Collection getLinkedModels(EntryLite entry)
			throws SQLException, ModelException, JDOMException {
		Collection models = new ArrayList();
		
		String sql = "SELECT " + ENTRY_MODEL_MODEL + " FROM " + ENTRY_MODEL_TABLE
				+ " WHERE " + ENTRY_MODEL_ENTRY + "=" + entry.getId()+";";
		Iterator itModelIds = DatabaseAdapter.executeSelectQuery(sql).iterator();
		
		while(itModelIds.hasNext()) {
			Long modelId = (Long)((Object[])itModelIds.next())[0];
			models.add(getModel(modelId));
		}
		
		return models;
	}
	
	/**
	 * Returns the entries linked to the model given in parameter.
	 * 
	 * @param model a <code>EntryModel</code> object.
	 * @return a Collection of <code>EntryLite</code> objects.
	 */
	public static Collection getLinkedEntries(EntryModel model)
			throws RemoteException, SQLException {
		Collection entries = new ArrayList();

		String sql = "SELECT " + ENTRY_MODEL_ENTRY + " FROM " + ENTRY_MODEL_TABLE
				+ " WHERE " + ENTRY_MODEL_MODEL + "=" + model.getId() + ";";
		Iterator itEntriesId = DatabaseAdapter.executeSelectQuery(sql).iterator();

		while (itEntriesId.hasNext()) {
			Long entryId = (Long) ((Object[]) itEntriesId.next())[0];
			entries.add(DataController.getValue(entryId, EntryLite.TYPE_ENT));
		}

		return entries;
	}
	
	/**
	 * Sets the models linked to an entry. Removes all the linked models and
	 * successively adds one.
	 * 
	 * @param entry
	 *          a <code>EntryLite</code> object.
	 * @param models
	 *          a Collection of <code>EntryModel</code> objects.
	 */
	public static void setLinkedModels(EntryLite entry, Collection models)
			throws SQLException {
		removeAllLinkedModels(entry);

		Iterator itModels = models.iterator();
		while (itModels.hasNext()) {
			link(entry, (EntryModel) itModels.next());
		}
	}
	
	/**
	 * Adds a link between the specified entry and model.
	 * 
	 * @param entry  a <code>EntryLite</code> object.
	 * @param model  a <code>EntryModel</code> object.
	 */
	public static void link(EntryLite entry, EntryModel model) throws SQLException {
		String sql = "INSERT INTO " + ENTRY_MODEL_TABLE + " VALUES ("
				+ entry.getId().toString() + "," + model.getId().toString() + ");";
		DatabaseAdapter.executeUpdateQuery(sql);
	}
	
	/**
	 * Sets the entries linked to a model. Removes all the linked entries and
	 * successively adds one.
	 * 
	 * @param model
	 *          a <code>EntryModel</code> object.
	 * @param entries
	 *          a Collection of <code>EntryLite</code> objects.
	 */
	public static void setLinkedEntries(EntryModel model, Collection entries)
			throws SQLException {
		removeAllLinkedEntries(model);

		Iterator itEntries = entries.iterator();
		while (itEntries.hasNext()) {
			link((EntryLite) itEntries.next(), model);
		}
	}
	
	/**
	 * Removes the link between the specified model and entry.
	 * 
	 * @param entry
	 *          a <code>EntryLite</code> object.
	 * @param model
	 *          a <code>EntryModel</code> object.
	 */
	public static void removeLink(EntryLite entry, EntryModel model)
			throws SQLException {
		String sql = "DELETE FROM " + ENTRY_MODEL_TABLE + " WHERE "
				+ ENTRY_MODEL_ENTRY + "=" + entry.getId().toString() + " AND "
				+ ENTRY_MODEL_MODEL + "=" + model.getId().toString();
		DatabaseAdapter.executeUpdateQuery(sql);
	}
	
	/**
	 * Specifies if there is a entry-model link between the specified entry and
	 * model.
	 * 
	 * @param entry
	 *          a <code>EntryLite</code> object.
	 * @param model
	 *          a <code>EntryModel</code> object.
	 * @return true if <code>model</code> belongs to the list of models linked
	 *         to <code>entry</code>.
	 */
	public static boolean existsLink(EntryLite entry, EntryModel model)
			throws SQLException, ModelException, JDOMException {
		return getLinkedModels(entry).contains(model);
	}
	
	/**
	 * Removes all the links between the specified entry and models.
	 * 
	 * @param entry
	 *          a <code>EntryLite</code> object.
	 */
	public static void removeAllLinkedModels(EntryLite entry) throws SQLException {
		String sql = "DELETE FROM " + ENTRY_MODEL_TABLE + " WHERE "
				+ ENTRY_MODEL_ENTRY + "=" + entry.getId().toString();
		DatabaseAdapter.executeUpdateQuery(sql);
	}

	/**
	 * Removes all the links between the specified model and entries.
	 * 
	 * @param model
	 *          a <code>EntryModel</code> object.
	 */
	public static void removeAllLinkedEntries(EntryModel model)
			throws SQLException {
		String sql = "DELETE FROM " + ENTRY_MODEL_TABLE + " WHERE "
				+ ENTRY_MODEL_MODEL + "=" + model.getId().toString();
		DatabaseAdapter.executeUpdateQuery(sql);
	}
}
